<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@page import="java.sql.*" %>
<%@page import="formularios.Conexion" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Consultar ventas</title>
<script language="javascript" src="popcalendar.js"></script>
<style type="text/css">
.Titulo {
	font-family: Arial, Helvetica, sans-serif;
	font-size: 18px;
}
.tituloTabla {
	font-family: Arial, Helvetica, sans-serif;
	color: #FFF;
	background-color: #00F;
	font-weight: bold;
}
.CentrarCon {
	text-align: center;
}
.aliniaValor {
	text-align: right;
}
</style>
</head>
<body>
<h2>Consultar ventas</h2>
<form action="ConsultaVentas.jsp" name="form1" method="post">
<fieldset>
<legend>Fechas a consultar:</legend>
<table>
   <tr>
      <td>Fecha inicial:</td>
      <td><input type="text" name = "txtfechaIni" id="txtfechaIni" onclick="popUpCalendar(this, form1.txtfechaIni, 'yyyy/mm/dd');"></td>
   </tr>
   <tr>
      <td>Fecha final:</td>
      <td><input type="text" name = "txtfechaFin" id="txtfechaFin" onclick="popUpCalendar(this, form1.txtfechaFin, 'yyyy/mm/dd');"></td>
   </tr>
</table> 
<br>
<input type="submit" value="Consultar" name="Btconsultar" id="Btconsultar">
</fieldset>
</form>
<br>
<%
    if (request.getParameter("Btconsultar") != null) {
    String fechaIni = request.getParameter("txtfechaIni");
    String fechaFin = request.getParameter("txtfechaFin");

	Conexion con = new Conexion();
   	//Esto es lo que Guarda
	try {
    	Connection cnn= con.getConeccion();//Abrimos nuestra coneccion
    	
    	// La consulta
        Statement st = cnn.createStatement();
        ResultSet rs = st.executeQuery("SELECT * FROM ventas WHERE fecha BETWEEN '"+fechaIni+"' AND '"+fechaFin+"'");
        //out.print(("SELECT * FROM ventas WHERE fecha BETWEEN '"+fechaIni+"' AND '"+fechaFin+"'"));
        	out.print("<table border='1' align='center'>"); 
        	out.print("<tr class='tituloTabla'>");
        	out.print("<td width='88'>Fecha");
        	out.print("<td width='43'>Cant");
        	out.print("<td width='354'>Descripcion");
        	out.print("<td width='66'>Valor");
        	out.print("<td width='75'>Total");
        	out.print("</tr>");
        
        	while(rs.next()){
        		out.print("<tr>");
        		out.print("<td>"+rs.getObject("fecha")+"</td>");
        		out.print("<td class='CentrarCon'>"+rs.getObject("cantidad")+"</td>");
        		out.print("<td>"+rs.getObject("descripcion")+"</td>");
        		out.print("<td class='aliniaValor'>"+rs.getObject("valor1")+"</td>");
        		out.print("<td class='aliniaValor'>"+rs.getObject("valor2")+"</td>");
        		out.print("</tr>");
        	}
        	out.print("</table>");

        	try{
    			// La consulta
        		Statement st2 = cnn.createStatement();
        		ResultSet rs2 = st2.executeQuery("SELECT SUM(valor2) as total FROM ventas WHERE FECHA BETWEEN '"+fechaIni+"' AND '"+fechaFin+"'");
        		rs2.next();
        
        		//------Imprime en Dato
        		if (rs2.getObject("total")!= null) {
        			out.println("<br>");
        			out.print("<span class='Titulo'>"); 
        			out.print("Vendido entre "+fechaIni+" y "+fechaFin+": "+rs2.getObject("total").toString());
        			out.print("</span>");
        		}
        
        	}catch(SQLException ex){
        		out.print(ex);        	
        	}

        	//Cerramos la coneccion
        cnn.close();
        

	} catch (SQLException ex) {
    	System.out.print(ex);
}   
    }
%>
<br>
<a href="index.html">Atras...</a>
</body>
</html>